USE Partitioning;
GO

-- split partition
ALTER PARTITION SCHEME psOrders
NEXT USED [PRIMARY];
GO

BEGIN TRANSACTION
	DECLARE @Year DATETIME;
	SET @Year = CAST((SELECT TOP 1 [value] FROM sys.partition_range_values
		WHERE function_id = (SELECT function_id 
			FROM sys.partition_functions
			WHERE name = 'pfOrders')
		ORDER BY boundary_id DESC) AS DATETIME);

	SET @Year = DATEADD(YEAR, 1, @Year);

	ALTER PARTITION FUNCTION pfOrders() 
	SPLIT RANGE (@Year);
COMMIT TRANSACTION

-- for a given partition function, list each of the boundary values
SELECT
	prv.boundary_id,
	prv.value
FROM sys.partition_range_values prv
	INNER JOIN sys.partition_functions pf ON prv.function_id = pf.function_id
WHERE pf.name = 'pfOrders'		-- partition function name
ORDER BY prv.value;

-- show the table partitions and rows inserted in the partitions
SELECT
	p.partition_number,
	fg.name,
	p.rows,
	p.index_id,
	au.total_pages,
	au.used_pages,
	p.data_compression_desc
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Orders')
AND p.index_id < 2;